package com.ysd.dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.ysd.entity.NewsCatalogue;
import com.ysd.util.DBManager;

public class NewsCatalogueDaoImpl {
	//新闻分类
	//访问修饰符 返回值类型 方法名（参数列表）{方法体}
	 public List<NewsCatalogue> getNewsList() {
		    List<NewsCatalogue> list=new ArrayList<NewsCatalogue>();//创建List
	    	Connection conn= DBManager.getConnection();//创建连接
	    	Statement st=null;
	    	ResultSet rs=null;
	    	NewsCatalogue newsCatalogue=null;
	    	try {//正常情况
				st=conn.createStatement();//创建Stetement，作用是执行SQL语句
				String sql="select * from newsCatalogue";//执行SQL语句
				rs=st.executeQuery(sql);
				while(rs.next()) {
					int newsCatalogueId=rs.getInt("newsCatalogueId");
					String newsCatalogueName=rs.getString("newsCatalogueName");
					newsCatalogue=new NewsCatalogue(newsCatalogueId,newsCatalogueName);
					list.add(newsCatalogue);
					
				}
				return list;
			} catch (SQLException e) {//非正常情况
				e.printStackTrace();
				return null;
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
	    }
	 //分页查询
	 public List<NewsCatalogue> getNewCataloguePage(int page,int size) {
		    List<NewsCatalogue> list=new ArrayList<NewsCatalogue>();//创建List
	    	Connection conn= DBManager.getConnection();//创建连接
	    	Statement st=null;
	    	ResultSet rs=null;
	    	NewsCatalogue newsCatalogue=null;
	    	try {//正常情况
				st=conn.createStatement();//创建Stetement，作用是执行SQL语句
				String sql="SELECT * FROM newscatalogue LIMIT "+(page-1)*size+","+size+"";//执行SQL语句
				rs=st.executeQuery(sql);
				while(rs.next()) {
					int newsCatalogueId=rs.getInt("newsCatalogueId");
					String newsCatalogueName=rs.getString("newsCatalogueName");
					newsCatalogue=new NewsCatalogue(newsCatalogueId,newsCatalogueName);
					list.add(newsCatalogue);
					
				}
				return list;
			} catch (SQLException e) {//非正常情况
				e.printStackTrace();
				return null;
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
	    }
	 /****
	  *根据Id 删除新闻分类
	  *
	  */
	 public int delNewsCatalogue(int id) {
		 return DBManager.executeUpdate("DELETE FROM newscatalogue WHERE newscatalogueId="+id);
	 }
	 
	/**
	 * 添加
	 */
	 public int addNewsCatalogue(NewsCatalogue nc) {
		 return DBManager.executeUpdate("insert into newscatalogue values(null,'"+nc.getNewsCatalogueName()+"')");
	 }
	 /***
	  * 根据名称查询分类
	  */
	 public  List<NewsCatalogue> getNewsCatalogueByName(String name){
		 List<NewsCatalogue> ncList=new ArrayList<NewsCatalogue>();
		 Connection conn=DBManager.getConnection();
		 Statement st=null;
		 ResultSet rs=null;
		 try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM newscatalogue WHERE newsCatalogueName='"+name+"'");
			while(rs.next()) {
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				String newsCatalogueName=rs.getString("newsCatalogueName");
				NewsCatalogue nc=new NewsCatalogue(newsCatalogueId,newsCatalogueName);
				ncList.add(nc);
			}
			return ncList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	 }
	 
	 //返回list是因为查到的结果集可能是多条
	 //返回单个对象是因为查到的结果集只能是一条
	 public NewsCatalogue getNewsCatalogueById(int cataId) {
		 NewsCatalogue nc=null;
		 Connection conn=DBManager.getConnection();
		 Statement st=null;
		 ResultSet rs=null;
		 try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM newscatalogue WHERE newsCatalogueId="+cataId);
			while(rs.next()) {
				int ncId=rs.getInt(1);
				String ncName=rs.getString(2);
				nc=new NewsCatalogue(ncId, ncName);
			}
			return nc;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	 }
	 
	 /**
	  * 修改新闻分类名称
	  */
	 public int updateNewsCatalogueById(NewsCatalogue nc) {
		 return DBManager.executeUpdate("UPDATE newscatalogue SET newsCatalogueName='"+nc.getNewsCatalogueName()+"' WHERE newsCatalogueId="+nc.getNewsCatalogueId()+"");
	 }
	 //批量删除
	 public int delectProductByIds(String ids) {
		 String sql="DELETE FROM newscatalogue WHERE newscatalogueId in("+ids+")";
		 System.out.println(sql);
		 return DBManager.executeUpdate(sql);
	}
}
